<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Accountrecharge_model extends CI_Model {
	
	function __construct(){
        parent::__construct();
    }
    
    function arrays($key){
    	$data = array(
    		'user_loan' 				=> array("0"=>"投资客", "1"=>"借款户"),
			'account_recharge_status' 	=> array("0"=>"系统：待审核", "2"=>"系统：审核拒绝","1"=>"系统：充值成功", "4"=>"宝付：处理中","3"=>"宝付：无充值订单"),
        	'account_recharge_type' 	=> array("0"=>"平台充值", "1"=>"网上充值", "2"=>"线下充值", "3"=>"积分兑换", "4"=>"发放代金券"),
    		'borrow_collection_status' 	=> array("0"=>"未还款", "1"=>"已还款"),
    		'user_self' 	=> array("0"=>"客户", "1"=>"马甲"),
    	);
    	
    	if($key && isset($data[$key])){
    		return $data[$key];
    	}else{
    		return array();
    	}
    }
    
    
	function add($data = array()){
		$this->db->insert('rsh_account_recharge', $data);
		return $this->db->insert_id();
	}
	
	function update($id, $data){
		$this->db->where('id', $id);
		$this->db->update('rsh_account_recharge', $data);
		return $this->db->affected_rows();
	}
    
	function get_accountrecharge_list($data){
		//封装查询内容
		$_select = "r.id, r.user_id, u.username, u.realname, u.is_loan, r.trade_no, r.fstatus, r.money, 
					r.payment, r.ftype, r.remark, r.fee, r.add_time, r.verify_userid, r.verify_time, r.verify_remark";
		
		//封装条件
		$_where = " where 1=1 ";
		if(isset($data['where']['user_id']) && $data['where']['user_id'] != ''){
			$_where .= " and r.user_id = '".$data['where']['user_id']."'";
		}
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and u.username like '%".$data['where']['username']."%'";
		}
		if(isset($data['where']['realname']) && $data['where']['realname'] != ''){
			$_where .= " and u.realname like '%".$data['where']['realname']."%'";
		}
		if(isset($data['where']['is_loan']) && $data['where']['is_loan'] != -1){
			$_where .= " and u.is_loan=".$data['where']['is_loan'];
		}
		if(isset($data['where']['fstatus']) && $data['where']['fstatus'] != -1){
			$_where .= " and r.fstatus=".$data['where']['fstatus'];
		}
		if(isset($data['where']['ftype']) && $data['where']['ftype'] != -1){
			$_where .= " and r.ftype=".$data['where']['ftype'];
		}
		if(isset($data['where']['strtime']) && $data['where']['strtime'] != 0){
			$strtime = strtotime($data['where']['strtime'].' 00:00:00');
			$_where .= " and r.verify_time >= ".$strtime;
		}
		if(isset($data['where']['endtime']) && $data['where']['endtime'] != 0){
			$endtime = strtotime($data['where']['endtime'].' 23:59:59');
			$_where .= " and r.verify_time <= ".$endtime;
		}
		$_order = " order by id desc ";
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		//封装sql
		$sql_result = "select $_select from rsh_account_recharge as r 
						left join rsh_user as u on r.user_id=u.user_id";
		$sql_result .= $_where;
		$sql_result .= $_order;
		
		
	
		
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['accountrecharge_list'] = $query->result_array();
			return $data;
		}else{
			$sql_result .= $_limit;
			$query = $this->db->query($sql_result);
			$data['accountrecharge_list'] = $query->result_array();
		}
		//echo $sql_result;exit;
		
		
		
		//总记录条数
		$sql_count = " select count(*) as count from rsh_account_recharge as r 
						left join rsh_user as u on r.user_id=u.user_id
						";
		$sql_count .= $_where;
		
		//echo $sql_count;exit();
		
		$query = $this->db->query($sql_count);
		$data['accountrecharge_count'] = $query->row()->count;
		
		//总金额
		$sql_count = " select sum(money) as total from rsh_account_recharge as r 
						left join rsh_user as u on r.user_id=u.user_id
						";
		$sql_count .= $_where;
		$query = $this->db->query($sql_count);
		$data['accountrecharge_total'] = $query->row()->total;
		
		return $data;
	}
	
	/**
	 * 获取用户各类充值汇总
	 */
	function get_recharge_group_sum($user_id){
		$sql = "select ftype,sum(money) as total_money from rsh_account_recharge where user_id='{$user_id}' and fstatus=1 group by ftype";
		$query = $this->db->query($sql);
		$result = $query->result_array();
		$list = array(
			'total' => 0
		);
		if($result){
			$t = 0;
			foreach($result as $v){
				$list[$v['ftype']] = $v['total_money'];
				$t = $t + $v['total_money'];
			}
			$list['total'] = $t;
		}
		return $list;
	}
	
	/**
	 * 统计用户充值手续费
	 */
	function get_recharge_fee_sum($user_id){
		$sql = "select sum(fee) as total_fee from rsh_account_recharge where user_id='{$user_id}' and fstatus=1";
		$query = $this->db->query($sql);
		$result = $query->row_array();
		return $result['total_fee'];
	}
	
	function get_one($where = array(),$select='*'){
		$this->db->select($select);
		if($where){
			$this->db->where($where);
		}
		$this->db->limit(1);
		$query = $this->db->get('rsh_account_recharge');
		return $query->row_array();
	}
	
	
	function changeOrderStatus(){
		
	}
	
	function onreturn($data = array()){
		$trade_no = $data['trade_no'];
		$ret = false;
		/*tangc 死锁问题*/
		$this->db->trans_strict(FALSE);
    	$this->db->trans_begin();
    
		$rechage_result = self::get_one(array("trade_no"=>$trade_no));
		
		if(isset($rechage_result['fstatus']) && ($rechage_result['fstatus']==0 || $rechage_result['fstatus']==4)){
			$user_id = $rechage_result['user_id'];
			//$user_id = substr($user_id,0,strlen($user_id)-1); 
			$time =time();
			$rec['id'] = $rechage_result['id'];
			$rec['freturn'] = $data['returnxml'];
			$rec['fstatus'] = 1;
			$rec['verify_userid'] = 0;
			//$rec['verify_time'] = $time;
			$rec['verify_remark'] = "成功充值";//mb_convert_encoding("成功充值","UTF-8","GBK");
			
			$ret = self::update($rechage_result['id'],$rec);
			if($ret){
				$this->load->model('Account_model', 'account');
				$account_result =  $this->account->get_account($user_id);	

				$log['user_id'] = $user_id;
				$log['ftype'] = RECHARGE;
				$log['money'] = $rechage_result['money'];
				
				$log['total'] = $account_result['total']+$rechage_result['money'];
				$log['use_money'] = $account_result['use_money']+$rechage_result['money'];
				$log['no_use_money'] = $account_result['no_use_money'];
				$log['collection'] = $account_result['collection'];
				$log['red_money'] = $account_result['red_money'];
				$log['to_user'] = 0;
				$log['add_time'] = $time;
				$log['addip'] = $this->input->ip_address();
				$log['remark'] = "在线充值";
				$ret = $this->account->addlog($log);
			}
			
			
			if($ret && $rechage_result['fee'] > 0){
				$account_result =  $this->account->get_account($user_id);	
				$log['user_id'] = $user_id;
				$log['ftype'] = FEE;
				$log['money'] =$rechage_result['fee'];
				//$log['money'] =0;
				$log['total'] = $account_result['total']-$log['money'];
				$log['use_money'] = $account_result['use_money']-$log['money'];
				$log['no_use_money'] = $account_result['no_use_money'];
				$log['collection'] = $account_result['collection'];
				$log['red_money'] = $account_result['red_money'];
				$log['to_user'] = 0;
				$log['add_time'] = $time;
				$log['addip'] = $this->input->ip_address();
				$log['remark'] = "在线充值手续费";
				$ret = $this->account->addlog($log);
			}
			
			//提醒设置
			$this->load->model('Message_model');
			//$parms = array('发送用户','接收用户','标题','状态','类型','是否保存发件箱','是否删除','内容','添加时间','添加IP');
			$msg = array(
				'sent_user' => '1',
				'receive_user' => $user_id,
				'name' => '充值到账',
				'fstatus' => '0',
				'message_type' => 'system',
				'sented' => '0',
				'deltype' => '0',
				'content' => '您于 '.date('Y-m-d H:i:s').' 充值到账：'.$rechage_result['money'].' 元，充值手续费：'.$rechage_result['fee'].' 元',
				'add_time' => $time,
				'addip' => $this->input->ip_address()
			);
			$this->Message_model->send($msg);
			
		}
		
		if ($this->db->trans_status() === TRUE) {
				$this->db->trans_commit();
				//奖励发放代金券
				$this->load->model('Redmanagement_model', 'redmanagement');
				$this->redmanagement->send_red($user_id,15,"充值奖励",$rechage_result['money']);
				$ret = self::update($rechage_result['id'],array('verify_time'=>$time));
				return 1;
	    } else {
	        $this->db->trans_rollback();
	        return 0;
	    }
		
		return $this->db->trans_status();
	}
	
	private function get_userid_by_username($username){
		$sql = "SELECT * FROM rsh_user WHERE username like '%".$username."%'";
		$query = $this->db->query($sql);
		$data = $query->result_array();
		$_uid = array();
		if($data){
			foreach ($data as $v){
				$_uid[] = $v['user_id'] ;
			}		
		}
		return $_uid;
	}
	
	function received_payments($data)
	{
		$_where = " ";
		
		if(isset($data['where']['username']) && $data['where']['username'] != ''){
			$_where .= " and p2.username = '".trim($data['where']['username'])."'";
		}
		
		if(isset($data['where']['kefu_username']) && $data['where']['kefu_username'] != ''){
			$_kefuid = $this->get_userid_by_username($data['where']['kefu_username']);
			if(!empty($_kefuid)) {
				$_where .= " and p4.kefu_userid in (".implode(",",$_kefuid).")";
			} 
		}
		
		if(isset($data['where']['fstatus']) && $data['where']['fstatus'] != -1){
			$_where .= " and p1.fstatus=".$data['where']['fstatus'];
		}		 
		
		if(isset($data['where']['is_self']) && $data['where']['is_self'] != -1){
			$_where .= " and p2.is_self=".$data['where']['is_self'];
		}
		
		if($data['where']['fstatus'] == 0){
			if(isset($data['where']['start_repay_time']) && $data['where']['start_repay_time'] != 0){
				$strtime = strtotime($data['where']['start_repay_time'].' 00:00:00');
				$_where .= " and p1.repay_time >= ".$strtime;
			}
			if(isset($data['where']['end_repay_time']) && $data['where']['end_repay_time'] != 0){
				$endtime = strtotime($data['where']['end_repay_time'].' 23:59:59');
				$_where .= " and p1.repay_time <= ".$endtime;
			}
		}elseif($data['where']['fstatus'] == 1){
			if(isset($data['where']['start_repay_time']) && $data['where']['start_repay_time'] != 0){
				$strtime = strtotime($data['where']['start_repay_time'].' 00:00:00');
				$_where .= " and p1.repay_yestime >= ".$strtime;
			}
			if(isset($data['where']['end_repay_time']) && $data['where']['end_repay_time'] != 0){
				$endtime = strtotime($data['where']['end_repay_time'].' 23:59:59');
				$_where .= " and p1.repay_yestime <= ".$endtime;
			}
		}
		
		/*if(isset($data['where']['start_repay_time']) && $data['where']['start_repay_time'] != 0){
			$start_time = strtotime($data['where']['start_repay_time'].' 00:00:00');
			$_where .= " and p1.repay_time >= ".$start_time;
		}
		
		if(isset($data['where']['end_repay_time']) && $data['where']['end_repay_time'] != 0){
			$end_time = strtotime($data['where']['end_repay_time'].' 23:59:59');
			$_where .= " and p1.repay_time <= ".$end_time;
		}*/
		
		$sql = 'SELECT p1.forder,p1.fstatus,p1.user_id,p2.username,p1.repay_account AS money,p3.title AS title,p3.time_limit ,p1.repay_time,p4.kefu_userid 
				FROM rsh_borrow_collection AS p1 
				LEFT JOIN rsh_user AS p2 ON p1.user_id=p2.user_id 
				LEFT JOIN rsh_borrow AS p3 ON p1.borrow_id=p3.id 
				LEFT JOIN rsh_user_vip AS p4 ON p1.user_id=p4.user_id 
				WHERE p3.fstatus=3 '.$_where.' ORDER BY p1.repay_time asc';
		
		$page_size = $data['page_size'];
		$page_start = $data['page_start'];
		
		
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql);			
		}else{
			$sql .= " limit $page_start, $page_size";
			$query = $this->db->query($sql);
		}
		
		$data['list'] = $query->result_array();
		
		//总记录条数
		$countsql = "SELECT COUNT(*) as count,SUM(p1.repay_account)as totalmoney 
					 FROM rsh_borrow_collection AS p1 
					 LEFT JOIN rsh_user AS p2 ON p1.user_id=p2.user_id 
					 LEFT JOIN rsh_borrow AS p3 ON p1.borrow_id=p3.id 
					 LEFT JOIN rsh_user_vip AS p4 ON p1.user_id=p4.user_id 
					 WHERE p3.fstatus=3 ".$_where;

		$qcount = $this->db->query($countsql);
		$r = $qcount->row();
		$data['count'] = $r->count;
		$data['totalmoney'] = $r->totalmoney;
		return $data;
			
	}
	
	// $fstatus = 1
	function get_user_recharge_total($user_id,$fstatus){
		$sql = "select sum(money) as total from rsh_account_recharge where user_id = {$user_id} and fstatus = {$fstatus} ";
		$query = $this->db->query($sql);
		
		$data = $query->row()->total;
		return $data;
		
	}
	

		
	// ###### by liub 20141110 
	// 充值总额 ,充值笔数,平均每笔充值金额 ,最高充值金额,最低充值金额,充值会员数 ,人均充值金额
	// 数组参数:年月日,季度值
	function get_recharge_statistics($data){
		$_select  = "r.verify_time,sum(r.money) as total_money,
			count(r.id) as total_items,
			sum(r.money)/count(r.id) as avg_money,
			max(r.money) as max_money,
			min(r.money) as min_moeny,
			count(distinct r.user_id) as total_user,
			sum(r.money)/count(distinct r.user_id) as avg_user_money";
		
		$_where = " where 1=1 ";
		
		// 时间戳范围
		if(isset($data['where']['strtime']) && $data['where']['strtime'] != 0){
			
			
			$strtime = strtotime($data['where']['strtime'].' 00:00:00');
			$_where .= " and r.verify_time >= ".$strtime;
		}
		if(isset($data['where']['endtime']) && $data['where']['endtime'] != 0){
			$endtime = strtotime($data['where']['endtime'].' 23:59:59');
			$_where .= " and r.verify_time <= ".$endtime;
		}
		
		$_group = " group by  DATE_FORMAT(FROM_UNIXTIME(r.verify_time),'%Y-%m-%d') ";
		$_order = " order by r.id desc ";
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		
		//封装sql
		$sql_result = "select $_select from rsh_account_recharge r LEFT JOIN rsh_user AS u ON r.user_id=u.user_id ";
		$_where .= " and  r.fstatus = 1 and u.is_self = 0 ";
		$sql_result .= $_where ;
		$sql_result .= $_group;
		$sql_result .= $_order;
		
		
		
		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['statisticsrecharge_list'] = $query->result_array();
			return $data;
		}else{
			$sql_result .= $_limit;
			$query = $this->db->query($sql_result);
			$data['statisticsrecharge_list'] = $query->result_array();
		}
		
		
		//总记录条数
		$sql_count = "select count(*) as count from (select 1 as count from rsh_account_recharge r LEFT JOIN rsh_user AS u ON r.user_id=u.user_id $_where $_group ) as vt";
		$query = $this->db->query($sql_count);
		$data['statisticsrecharge_count'] = $query->row()->count;
		
		return $data;
	}
	
	
	// ###### by liub 20141112
	function get_platform_statistics($data){
		//TODO  理财管理费 需加入进去
		//TODO  VIP 费用计算有问题
		$_select  = "time,
			sum(case when type = 1 then money else 0 end  ) as recharge_money,
			sum(case when type = 2 then money else 0 end  ) as cash_money,
			sum(case when type = 3 then money else 0 end  ) as vip_money ";
		
		$_where1 = " where r.fstatus = 1";
		$_where2 = " where c.fstatus = 1";
		$_where3 = " where 1=1 ";
		
		$_from1 = "select r.verify_time as time ,r.fee as money,1 as type from rsh_account_recharge r LEFT JOIN rsh_user AS u1 ON r.user_id=u1.user_id ";	
		$_from2 = "select c.add_time ,c.fee ,2 from rsh_account_cash c LEFT JOIN rsh_user AS u2 ON c.user_id=u2.user_id ";
		$_from3 = "select user.vip_verify_time ,user.vip_money ,3 from rsh_user_vip user LEFT JOIN rsh_user AS u3 ON user.user_id=u3.user_id ";
		
		// 时间戳范围
		if(isset($data['where']['strtime']) && $data['where']['strtime'] != 0){
			$strtime = strtotime($data['where']['strtime'].' 00:00:00');
			$_where1 .= " and r.verify_time >= ".$strtime;
			$_where2 .= " and c.add_time >= ".$strtime;
			$_where3 .= " and user.vip_verify_time >= ".$strtime;
		}
		if(isset($data['where']['endtime']) && $data['where']['endtime'] != 0){
			$endtime = strtotime($data['where']['endtime'].' 23:59:59');
			$_where1 .= " and r.verify_time <= ".$endtime;
			$_where2 .= " and c.add_time <= ".$endtime;
			$_where3 .= " and user.vip_verify_time <= ".$endtime;
		}
		
		$_where1 .= " and u1.is_self = 0 ";
		$_where2 .= " and u2.is_self = 0 ";
		$_where3 .= " and u3.is_self = 0 ";
		$_from1 .= $_where1 ;
		$_from2 .= $_where2 ;
		$_from3 .= $_where3 ;
		
		$_from = "($_from1  UNION ALL $_from2   UNION ALL $_from3  ) as vt";
		$_group = " group by  DATE_FORMAT(FROM_UNIXTIME(vt.time),'%Y%m%d') ";
		$_order = " order by vt.time desc";
		$_limit = " limit ".$data['page_start'].",".$data['page_size'];
		
		//封装sql
		$sql_result = "select $_select  from $_from";
		$sql_result .= $_group;
		$sql_result .= $_order;

		if(isset($data['limit']) && $data['limit'] == 'all'){
			$query = $this->db->query($sql_result);
			$data['statisticsplatform_list'] = $query->result_array();
			return $data;
		}else{
			$sql_result .= $_limit;
			$query = $this->db->query($sql_result);
			$data['statisticsplatform_list'] = $query->result_array();
		}
		
		//总记录条数,分页汇总
		$sql_count = "select count(*) as count from (select 1 from $_from $_group) as vt2";
		$query = $this->db->query($sql_count);
		$data['statisticsplatform_count'] =  $query->row()->count;

		return $data;
	}
	
			function queryFloatCount(){
		// 目前账户可用总额
		$_acct_sum_money = " select sum(use_money)  as total_use_money from rsh_account ";
		$query_sum = $this->db->query($_acct_sum_money);
		$data['sum_money'] = $query_sum->row()->total_use_money;
		
		// 有可用余额的用户数量
		$_acct_count_user = " select count(id) as total_use_count from rsh_account  where use_money != 0 ";
		$query_count = $this->db->query($_acct_count_user);
		$data['count_user'] = $query_count->row()->total_use_count;
		
		$data['avg_money']  = $data['sum_money'] /$data['count_user'] ;
		
		return $data;
	}
}